﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DPhonePlatform
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsNumber(string phonenumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tphoneplatform");
            strSql.Append(" where  Number=@Number");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Number", MySqlDbType.VarChar,15)
                };
            parameters[0].Value = phonenumber;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }

         

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.PhonePlatformInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tphoneplatform(");
            strSql.Append("Number,LoginCount,FirstLoginTime,LastLoginTime,");
            strSql.Append("Version)");
            strSql.Append(" values (");
            strSql.Append("@Number,@LoginCount,@FirstLoginTime,@LastLoginTime,");
            strSql.Append("@Version)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Number", MySqlDbType.VarChar,15),
					new MySqlParameter("@LoginCount", MySqlDbType.Int32,20),
					new MySqlParameter("@FirstLoginTime", MySqlDbType.DateTime),
                    new MySqlParameter("@LastLoginTime", MySqlDbType.DateTime),                   
                    new MySqlParameter("@Version", MySqlDbType.VarChar,10)};
            parameters[0].Value = model.Number;
            parameters[1].Value = model.LoginCount;
            parameters[2].Value = model.FirstLoginTime;
            parameters[3].Value = model.LastLoginTime;
            parameters[4].Value = model.Version;
             
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateAfterLogin(string number,string version)
        {   // OverlayName,Overlaytype,City,Longitude,Latitude,Star,Remark
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tphoneplatform set ");           
            strSql.Append("LoginCount=LoginCount+1,");
            strSql.Append("LastLoginTime=@LastLoginTime,");                    
            strSql.Append("Version=@Version ");
            strSql.Append("where Number=@Numer");
            MySqlParameter[] parameters = {
                                             
                new MySqlParameter("@LastLoginTime", MySqlDbType.DateTime),                                              
                new MySqlParameter("@Version", MySqlDbType.VarChar,10),
                new MySqlParameter("@Number", MySqlDbType.VarChar)
                                          };
            parameters[0].Value = DateTime.Now;
            parameters[1].Value = version;
            parameters[2].Value = number;             
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateArea(string number, string area)
        {   // OverlayName,Overlaytype,City,Longitude,Latitude,Star,Remark
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tphoneplatform set "); 
            strSql.Append("Area=@Area ");
            strSql.Append("where Number=@Number");
            MySqlParameter[] parameters = {                                              
                new MySqlParameter("@Area", MySqlDbType.VarChar,50),
                new MySqlParameter("@Number", MySqlDbType.VarChar,15)
                                          };
            parameters[0].Value = area;
            parameters[1].Value = number; 
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tphoneplatform ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
       
       
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("select  t.id,t.PhoneNumber,t.LoginCount,t.LastLoginTime,");           
            //strSql.Append("IF(d.Times IS NULL,0,d.Times) as Times");
            //strSql.Append(" From tphoneuser t left join (select count(*) ");
            //strSql.Append("as times,phoneNumber from  ttrade  where Type='1' ");
            strSql.Append("select  * ");
            strSql.Append(" FROM tphoneplatform ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            //strSql.Append(" group by phoneNumber)d on t.PhoneNumber = d.phoneNumber");                        
            //strSql.Append(" order by LastLoginTime desc");
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            return ds;
        }



        public DataSet GetList(string parameter, string parameterVaule)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  id,Number,FirstLoginTime,");
            strSql.Append("LastLoginTime,LoginCount,Version,Area ");
            strSql.Append("FROM tphoneplatform ");
            strSql.Append(" where ");
            strSql.Append(parameter);
            strSql.Append(" like @A");
            MySqlParameter[] parameters = {
                                              new MySqlParameter("@A", MySqlDbType.Text)
                                          };
            parameters[0].Value = "%" + parameterVaule + "%";
            return DbHelperMySQL.Query(strSql.ToString(), parameters);
        }


        //// 按规定各个时间段内登录人次统计
        //public DataSet GetReportByTime(int dateType, string strWhere)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select count(*) as LoginCount,");
        //    strSql.Append("(select count(*) from tphoneuser) as UserCount,");
        //    strSql.Append("((select count(*) from tphoneuser) -count(*)) as NotLogin");
        //    if (dateType != 0)
        //    {
        //        switch (dateType)
        //        {
        //            case 1:  //按小时分组
        //                //strSql.Append(",DATE_FORMAT(LastLoginTime, '%H' ) AS DT ");
        //                strSql.Append(",DATE_FORMAT(FirstLoginTime, '%H点' ) AS DT ");
        //                break;
        //            case 2: //按天分组
        //                //strSql.Append(",DATE_FORMAT(LastLoginTime, '%Y-%m-%d' ) AS DT ");
        //                strSql.Append(",DATE_FORMAT(FirstLoginTime, '%d' ) AS DT ");
        //                break;
        //            case 3:  //按月分组
        //                //strSql.Append(",DATE_FORMAT(LastLoginTime, '%Y-%m' ) AS DT ");
        //                strSql.Append(",DATE_FORMAT(FirstLoginTime, '%m月' ) AS DT ");
        //                break;
        //            case 4: //按年分组
        //                //strSql.Append(",DATE_FORMAT(LastLoginTime, '%Y' ) AS DT ");
        //                strSql.Append(",DATE_FORMAT(FirstLoginTime, '%Y年' ) AS DT ");
        //                break;
        //        }
        //    }
        //    strSql.Append(" From tphoneuser ");
        //    if (strWhere.Trim() != "")
        //    {
        //        strSql.Append(" where ");
        //        strSql.Append(strWhere);
        //    }
        //    strSql.Append(" group by DT order by DT asc");
        //    return DbHelperMySQL.Query(strSql.ToString());
        //}

        ////按手机用户登录次数统计
        //public DataSet GetReportByLoginCount(int[] rangeValue, string strWhere)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select ");
        //    for (int i = 0; i < rangeValue.Length; i++)
        //    {
        //        strSql.Append("count(case when LoginCount");
        //        if (i == 0)
        //        {
        //            strSql.Append("<");
        //            strSql.Append(rangeValue[0]);
        //        }
        //        //else if(i == rangeValue.Length -1)
        //        //{
        //        //    strSql.Append(">");
        //        //    strSql.Append(rangeValue[i]);
        //        //}
        //        else
        //        {
        //            strSql.Append(">=");
        //            strSql.Append(rangeValue[i - 1]);
        //            strSql.Append(" and LoginCount <");
        //            strSql.Append(rangeValue[i]);
        //        }
        //        strSql.Append(" then LoginCount end) as ");
        //        strSql.Append("A" + i);
        //        strSql.Append(",");
        //        //if (i != rangeValue.Length - 1)
        //        //    strSql.Append(",");

        //    }
        //    //将最后的数值-该数值以上记为一列
        //    strSql.Append("count(case when LoginCount");
        //    strSql.Append(">=");
        //    strSql.Append(rangeValue[rangeValue.Length - 1]);
        //    strSql.Append(" then LoginCount end) as ");
        //    strSql.Append("A" + rangeValue.Length);

        //    strSql.Append(" FROM tphoneuser t ");
        //    if (strWhere.Trim() != "")
        //    {
        //        strSql.Append("where ");
        //        strSql.Append(strWhere);
        //    }
        //    return DbHelperMySQL.Query(strSql.ToString());
        //}

        //// 按用户交易次数统计
        //public DataSet GetReportByTradeTimes(int[] rangeValue, string strWhere)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select count(case when Times=0 then Times end) as A0,");
        //    for (int i = 0; i < rangeValue.Length; i++)
        //    {
        //        strSql.Append("count(case when Times");
        //        if (i == 0)
        //        {
        //            strSql.Append(">0 and Times");
        //            strSql.Append("<");
        //            strSql.Append(rangeValue[0]);
        //        }
        //        else
        //        {
        //            strSql.Append(">=");
        //            strSql.Append(rangeValue[i - 1]);
        //            strSql.Append(" and Times <");
        //            strSql.Append(rangeValue[i]);
        //        }
        //        strSql.Append(" then Times end) as ");
        //        strSql.Append("A" + (i + 1));
        //        strSql.Append(",");
        //    }
        //    //将最后的数值-该数值以上记为一列
        //    strSql.Append("count(case when Times ");
        //    strSql.Append(">=");
        //    strSql.Append(rangeValue[rangeValue.Length - 1]);
        //    strSql.Append(" then Times  end) as ");
        //    strSql.Append("A" + (rangeValue.Length + 1));

        //    strSql.Append(" FROM ");
        //    strSql.Append("(SELECT t.PhoneNumber,t.LoginCount,t.LastLoginTime,");
        //    strSql.Append("IF(d.times IS NULL,0,times) as times");
        //    strSql.Append(" FROM tphoneuser t left join (select count(*) as times,");
        //    strSql.Append("phoneNumber from ttrade  where Type='1' ");
        //    if (strWhere.Trim() != "")
        //    {
        //        strSql.Append("and ");
        //        strSql.Append(strWhere);
        //    }
        //    strSql.Append(" group by phoneNumber)d");
        //    strSql.Append(" on t.PhoneNumber = d.phoneNumber) TT");
        //    return DbHelperMySQL.Query(strSql.ToString());
        //}

        //public int GetCount(string parameter, string parameterValue)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select count(*) from tphoneuser ");
        //    if (parameter.Trim() != "" && parameterValue.Trim() != "")
        //    {
        //        strSql.Append("where ");
        //        strSql.Append(parameter);
        //        strSql.Append(" like '%");
        //        strSql.Append(parameterValue);
        //        strSql.Append("%'");
        //    }
        //    DataSet ds = DbHelperMySQL.Query(strSql.ToString());
        //    if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1)
        //    {
        //        return -1;
        //    }
        //    return int.Parse(ds.Tables[0].Rows[0][0].ToString());
        //}

        //public DataSet GetListByPage(int page, int pagesize, string parameter, string parameterValue)
        //{
        //    if (parameterValue == null)
        //    {
        //        return null;
        //    }
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select  t.id,t.PhoneNumber,");
        //    strSql.Append("(case t.type when '0' then '安卓系统' ");
        //    strSql.Append("when '1' then 'IOS系统' end ) as type,");
        //    strSql.Append("t.PhoneIMEI,t.PhoneVersion,t.FirstLoginTime,");
        //    strSql.Append("t.LastLoginTime,t.LoginCount,");
        //    strSql.Append("IF(d.Times IS NULL,0,d.Times) as Times,t.Recommender");
        //    strSql.Append(" From tphoneuser t left join (select count(*) ");
        //    strSql.Append("as times,phoneNumber from  ttrade  where Type='1' ");
        //    strSql.Append("group by phoneNumber)d on t.PhoneNumber = d.phoneNumber ");
        //    if (parameter.Trim() != "" && parameterValue != "")
        //    {
        //        strSql.Append("where t.");
        //        strSql.Append(parameter);
        //        strSql.Append(" like '%");
        //        strSql.Append(parameterValue);
        //        strSql.Append("%'");
        //    }
        //    strSql.Append(" order by LastLoginTime desc ");
        //    strSql.Append("limit ");
        //    strSql.Append((page - 1) * pagesize);
        //    strSql.Append(",");
        //    strSql.Append(pagesize);
        //    DataSet ds = DbHelperMySQL.Query(strSql.ToString());
        //    return ds;

        //}
 


    }
}
